﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using HK2009.Common;
using HK2009.BusinessFacade;
using HK2009.BusinessFacadeHandler;
using HK2009.EntityQuery;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;

public partial class SystemManage_DestinationBatchImport : BasePage
{
    ISyDestinationHandler desManager = new SyDestinationHandler();

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private DataTable NameExists(string countryCN,string countryHK,string countryEN,string nameCN,string nameHK,string nameEN)
    {
       
        SqlParameter[] prams = new SqlParameter[6];
        prams[0] = new SqlParameter("@Country", countryCN);
        prams[1] = new SqlParameter("@CNName", nameCN);
        prams[2] = new SqlParameter("@HKName", nameHK);
        prams[3] = new SqlParameter("@ENGName", nameEN);
        prams[4] = new SqlParameter("@HKCountry", countryHK);
        prams[5] = new SqlParameter("@ENGCountry", countryEN);
        string sql = "select * from Sy_Destination where (Country = @Country or HKCountry = @HKCountry or  ENGCountry = @ENGCountry ) and (CNName = @CNName or HKName = @HKName or ENGName = @ENGName)";
        DataTable dt = desManager.GetList(sql, prams);

        return dt;
    }


    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (upFile.FileName != string.Empty)
        {
            if (Path.GetExtension(upFile.FileName) == ".xls" || Path.GetExtension(upFile.FileName) == ".xlsx")
            {

                string fileUrl = FileTools.MakeDirByMonth("/ImportTemplete") + FileTools.AutoFileName() + Path.GetExtension(upFile.FileName);
                //保存文件
                upFile.SaveAs(Server.MapPath("~").ToString() + fileUrl);


                DataTable dt = BackupToExcel.IpmortExcel(System.Web.HttpContext.Current.Server.MapPath(fileUrl));
                string strFailed = string.Empty;

                try
                {
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i]["Name(CN)"].ToString() != string.Empty || dt.Rows[i]["Name(EN)"].ToString() != string.Empty || dt.Rows[i]["Name(HK)"].ToString() != string.Empty)
                            {
                                DataTable dtDes = NameExists(dt.Rows[i]["Nation(CN)"].ToString(), dt.Rows[i]["Nation(HK)"].ToString(), dt.Rows[i]["Nation(EN)"].ToString(), dt.Rows[i]["Name(CN)"].ToString(), dt.Rows[i]["Name(HK)"].ToString(), dt.Rows[i]["Name(EN)"].ToString());
                                if (dtDes != null && dtDes.Rows.Count > 0)
                                {
                                    if (strFailed != "")
                                    {
                                        strFailed += ",";
                                    }
                                    strFailed += dt.Rows[i]["Name(EN)"].ToString();
                                }
                                else
                                {
                                    Sy_DestinationQuery query = new Sy_DestinationQuery();
                                    query.Country = dt.Rows[i]["Nation(CN)"].ToString();
                                    query.Code = string.Empty;
                                    query.HKCountry = dt.Rows[i]["Nation(HK)"].ToString();
                                    query.ENGCountry = dt.Rows[i]["Nation(EN)"].ToString();

                                    query.CNName = dt.Rows[i]["Name(CN)"].ToString();
                                    query.HKName = dt.Rows[i]["Name(HK)"].ToString();
                                    query.ENGName = dt.Rows[i]["Name(EN)"].ToString();


                                    int status = 1;
                                    int.TryParse(dt.Rows[i]["Status"].ToString(), out status);
                                    query.Status = status;
                                                                       
                                    query.LastModDate = DateTime.Now;
                                    if (Session["UserID"] != null)
                                    {
                                        query.LastModUser = int.Parse(Session["UserID"].ToString());
                                    }

                                    else
                                    {
                                        query.LastModUser = 0;
                                    }

                                    desManager.AddDestination(query);
                                }
                            }
                        }

                        if (strFailed != "")
                        {
                            Jscript.Alert("导入成功，其中有部分数据因为已经存在，无法导入.这些数据是：" + strFailed, this.Page);
                        }
                        else
                        {
                            Jscript.Alert("导入成功", this.Page);
                        }
                    }

                    else
                    {
                        Jscript.Alert("无数据信息导入，请检查导入的文件是否有数据或者文件格式是否正确！", this.Page);
                    }
                }
                catch
                {
                    Jscript.Alert("导入过程中发生了异常，请与管理员联系！");

                }
            }
            else
            {
                Jscript.Alert("导入格式错误,导入的文件必须是.xls格式!", this.Page);
                return;
            }
        }
        else
        {
            Jscript.Alert("请先上传要导入的文件!", this.Page);
            return;
        }
    }
    protected void btnReset_Click(object sender, EventArgs e)
    {
        Response.Redirect("DestinationList.aspx");
    }

 
}
